﻿using System;
using System.Collections.Generic;
using System.Web;
using System.IO;
using System.Data;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Reflection;

namespace DataObject
{
    public abstract class IDataObject
    {
        protected FileManager engine;
        protected List<FieldInfo> fieldInfos;
        private List<string> fieldNames;
        private IDbConnection conn;
        private List<IDataObject> dataObjects;
        private string className;
        private string fileName;
        private string fullPath;
        //private FileStream filestream;

        #region Public Properties
        public string FileName
        {
            get { return fileName; }
            set { fileName = value; }
        }

        public string FullPath
        {
            get { return fullPath; }
            set { fullPath = value; }
        }
        #endregion

        #region Public Methods
        public Object[] enquireDataFromDb()
        {
            List<Object> retVal = new List<object>();
            IDbConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["SurveyConnectionString"].ConnectionString);
            conn.Open();            
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT * FROM " + this.className;

            IDataReader dr = cmd.ExecuteReader();
            DataColumnCollection columns = dr.GetSchemaTable().Columns;
            while (dr.Read())
            {
                ConstructorInfo constructor = engine.classType.GetConstructors()[0];
                Object obj = constructor.Invoke(null);
                foreach (DataColumn column in columns)
                {
                    for (int i = 0; i < fieldNames.Count; i++)
                    {
                        fieldInfos[i].SetValue(obj, dr.GetValue(i+1));
                    }
                }
                retVal.Add(obj);
            }
            return retVal.ToArray();
        }

        public void InsertData()
        {
            StreamReader sr = new StreamReader(fullPath);
            dataObjects = readDataObjects(sr);
            delete_InsertRecords();
            sr.Close();
            sr.Dispose();
        }

        #endregion

        public IDataObject()
        {
            setFileName();
            fullPath = Path.Combine(HttpContext.Current.Server.MapPath("../tmp"), fileName);
            this.className = this.GetType().Name;
            engine = new FileManager(this.GetType());
            conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["SurveyConnectionString"].ConnectionString);
            fieldInfos = new List<FieldInfo>(engine.fieldsInfos);
            fieldNames = engine.fieldNames;
        }

        protected List<IDataObject> readDataObjects(StreamReader sr)
        {
            
            List<IDataObject> dataObjects = new List<IDataObject>();
            Object[] dataRecords = engine.ReadStream(sr);
            foreach (Object obj in dataRecords)
            {
                dataObjects.Add((IDataObject)obj);
            }
            return dataObjects;
        }


        protected void delete_InsertRecords()
        {
            using (conn)
            {
                conn.Open();
                IDbTransaction tran = conn.BeginTransaction();
                try
                {
                    IDbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = createDeletSqlscript();
                    cmd.Transaction = tran;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = createInsertSqlscript();
                    foreach (FieldInfo fieldInfo in fieldInfos)
                    {
                        IDbDataParameter paremeter = cmd.CreateParameter();
                        setSqlParameter(paremeter, fieldInfo);
                        cmd.Parameters.Add(paremeter);
                    }

                    foreach (IDataObject dataObject in dataObjects)
                    {
                        for (int i = 0; i < cmd.Parameters.Count; i++)
                        {
                            setParameterValue((IDbDataParameter)cmd.Parameters[i], i, dataObject);
                        }
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw ex;
                }
            }

        }

        protected abstract void setFileName();  

        private string createDeletSqlscript()
        {
            return @"DELETE FROM " + className;
        }

        private string createInsertSqlscript()
        {
            string sqlscript = @"INSERT INTO" + "[" + className + "](";
            string tmp = ") VALUES(";
            for (int i = 0; i < fieldNames.Count - 1; i++)
            {
                sqlscript += fieldNames[i];
                sqlscript += ",";

                tmp += "@" + fieldNames[i];
                tmp += ",";
            }
            sqlscript += fieldNames[fieldNames.Count - 1];
            tmp += "@" + fieldNames[fieldNames.Count - 1] + ")";
            sqlscript += tmp;
            return sqlscript;
        }

        private void setSqlParameter(IDbDataParameter paremeter, FieldInfo fieldInfo)
        {
            paremeter.DbType = DBTypeMapping.GetDbType(fieldInfo.FieldType);
            paremeter.ParameterName = fieldInfo.Name;
        }

        private void setParameterValue(IDbDataParameter paremeter, int index, IDataObject dataObject)
        {
            paremeter.Value = fieldInfos[index].GetValue(dataObject);
        }
    }
}